'''
Created on Oct 8, 2012

@author: peng
'''
import os
import MySQLdb


BELUGA_DB_CONFIG = {'host': '10.42.133.171',
                    'user': 'dev_api',
                    'password': 'api_dev_pwd',
                    'db': 'beluga_api',
                    'port': 3306}


class MySQLdbWrapper:

    conn = None

    def connect(self):
        self.conn = MySQLdb.connect(host=BELUGA_DB_CONFIG['host'],
                                    user=BELUGA_DB_CONFIG['user'],
                                    passwd=BELUGA_DB_CONFIG['password'],
                                    db=BELUGA_DB_CONFIG['db'],
                                    port=BELUGA_DB_CONFIG['port'])
        self.conn.set_character_set('utf8')
        self.conn.autocommit(True)

    def cursor(self):
        try:
            self.connect()
            return self.conn.cursor()
        except MySQLdb.OperationalError:
            self.connect()
            return self.conn.cursor()

_db = MySQLdbWrapper()


def get_data(sql):
    try:
        cursor = _db.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        return data
    finally:
        cursor.close()

path = '/mnt/export/user-data'

# retention_daily
sql_d = \
'''
select app_key, date, promotion_id, app_version, channel,
`1`,`2`,`3`,`4`,`5`,`6`,`7`,`8`,`9`
from retention_daily
where date<'2012-10-01'
'''

# retention_weekly
sql_w = \
'''
select app_key, date, promotion_id, app_version, channel,
`1`,`2`,`3`,`4`,`5`,`6`,`7`,`8`
from retention_weekly
where date<'2012-10-01'
'''

# retention_monthly
sql_m = \
'''
select app_key, date, promotion_id, app_version, channel,
`1`,`2`,`3`,`4`,`5`,`6`
from retention_monthly
where date<'2012-10-01'
'''


def remove_control_characters(line):
    # mpa = dict.fromkeys(range(32))
    # line = line.translate(mpa)
    return line


def flush(data_list, file_name):
    if len(data_list) == 0:
        return
    f = open(os.path.join(path, file_name), 'a+')
    data = '\n'.join(data_list)
    f.write(data)
    f.write('\n')
    f.close()


def export_daily():
    data_list = []
    rows = get_data(sql_d)
    for row in rows:
        l = []
        l.append(row[0])
        l.append(str(row[1])[:10].replace('-', ''))
        l.append(row[2])
        l.append(row[3])
        l.append(row[4])
        l.append('0')
        l.append('-')
        if row[5] != -1:
            l.append('1:%s' % row[5])
        if row[6] != -1:
            l.append('2:%s' % row[6])
        if row[7] != -1:
            l.append('3:%s' % row[7])
        if row[8] != -1:
            l.append('4:%s' % row[8])
        if row[9] != -1:
            l.append('5:%s' % row[9])
        if row[10] != -1:
            l.append('6:%s' % row[10])
        if row[11] != -1:
            l.append('7:%s' % row[11])
        if row[12] != -1:
            l.append('8:%s' % row[12])
        if row[13] != -1:
            l.append('9:%s' % row[13])
        line = '\t'.join(l)
        data_list.append(remove_control_characters(line))
    flush(data_list, 'd-retention')


def export_weekly():
    data_list = []
    rows = get_data(sql_w)
    for row in rows:
        l = []
        l.append(row[0])
        l.append(str(row[1])[:10].replace('-', ''))
        l.append(row[2])
        l.append(row[3])
        l.append(row[4])
        l.append('0')
        l.append('-')
        if row[5] != -1:
            l.append('1:%s' % row[5])
        if row[6] != -1:
            l.append('2:%s' % row[6])
        if row[7] != -1:
            l.append('3:%s' % row[7])
        if row[8] != -1:
            l.append('4:%s' % row[8])
        if row[9] != -1:
            l.append('5:%s' % row[9])
        if row[10] != -1:
            l.append('6:%s' % row[10])
        if row[11] != -1:
            l.append('7:%s' % row[11])
        if row[12] != -1:
            l.append('8:%s' % row[12])
        line = '\t'.join(l)
        data_list.append(remove_control_characters(line))
    flush(data_list, 'w-retention')


def export_monthly():
    data_list = []
    rows = get_data(sql_w)
    for row in rows:
        l = []
        l.append(row[0])
        l.append(str(row[1])[:10].replace('-', ''))
        l.append(row[2])
        l.append(row[3])
        l.append(row[4])
        l.append('0')
        l.append('-')
        if row[5] != -1:
            l.append('1:%s' % row[5])
        if row[6] != -1:
            l.append('2:%s' % row[6])
        if row[7] != -1:
            l.append('3:%s' % row[7])
        if row[8] != -1:
            l.append('4:%s' % row[8])
        if row[9] != -1:
            l.append('5:%s' % row[9])
        if row[10] != -1:
            l.append('6:%s' % row[10])
        line = '\t'.join(l)
        data_list.append(remove_control_characters(line))
    flush(data_list, 'm-retention')


if __name__ == '__main__':
    export_daily()
    export_weekly()
    export_monthly()
